package com.pro01.backend.mapper;

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

@Mapper
public interface RatioMapper {

    /**
     * 查询某用户在 tracker_reports 表中 event 为 COMPLETED 的下载流量总和
     */
    @Select("SELECT SUM(downloaded) " +
            "FROM tracker_reports " +
            "WHERE user_id = #{userId} AND event = 'COMPLETED'")
    Long sumDownloadByUser(@Param("userId") Long userId);

    /**
     * 估算某用户的上传流量：统计其发布的种子被下载的次数 × 每个种子的大小
     */
    @Select("""
        SELECT COALESCE(SUM(t.size * r.download_count), 0)
        FROM torrents t
        LEFT JOIN (
            SELECT torrent_id, COUNT(*) AS download_count
            FROM tracker_reports
            WHERE event = 'COMPLETED'
            GROUP BY torrent_id
        ) r ON t.id = r.torrent_id
        WHERE t.user_id = #{userId}
    """)
    Long estimateUploadByUploader(@Param("userId") Long userId);

    @Select("""
    SELECT COALESCE(SUM(reward_uploaded), 0)
    FROM user_promotions2
    WHERE user_id = #{userId}
""")
    Long sumRewardUploaded(@Param("userId") Long userId);

    @Select("""
    SELECT COALESCE(SUM(reward_downloaded), 0)
    FROM user_promotions2
    WHERE user_id = #{userId}
""")
    Long sumRewardDownloaded(@Param("userId") Long userId);

}

